package main

import (
	"fmt"
	"github.com/gin-gonic/gin"
	"log"
	"net/http"
	"strconv"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

var db *sqlx.DB

//sqlx 是对 sql 进行的一层封装
func initializeDatabase() (err error) {
	//data source name
	//用户名@密码@协议(ip:port)/数据库
	dsn := "root:123456@tcp(gincloud:3306)/gin_demo?charset=utf8mb4&parseTime=True&loc=Local"
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Println("connect 2 database failed...")
		return
	}

	//这里可以不用ping了, sqlx 进行了处理

	//最大连接数
	db.SetMaxOpenConns(10)
	//最大闲置连接数
	db.SetMaxIdleConns(10)
	return nil
}

type user struct {
	//如果需要返回前端, 那么首字母需要大写(小写为私有)
	//前端展示效果可以通过 `json:"id"` 来处理
	//使用 sqlx 必须大写, 不然无法进行反射
	//db 指定数据库的字段名(如不指定则默认首字母小写)
	Id   uint64 `json:"id" db:"id"`
	Name string `json:"name" db:"name"`
	Age  uint32 `json:"age"`
}

func querySingleRow(id uint64) user {
	var u user
	sqlStr := "select * from student where id = ?"
	//单条查询使用 Get
	//Get内部帮我们实现了 scan
	if err := db.Get(&u, sqlStr, 1); err != nil {
		log.Printf("scan failed err : %v\n", err)
	}

	log.Println(u.Id, u.Name, u.Age)
	return u
}

func queryMultiRow() []user {
	sqlStr := "select * from student where id >= ?"
	var users []user
	//查询多条使用 Select
	if err := db.Select(&users, sqlStr, 5); err != nil {
		log.Printf("scan failed err : %v\n", err)
		return nil
	}

	return users
}

func updateRow() {
	sqlStr := "update student set name=? where id = ?"
	//Get内部帮我们实现了 scan
	res, err := db.Exec(sqlStr, "ginx", 1)

	if err != nil {
		log.Printf("update failed err : %v\n", err)
	}
	affectedNum, err := res.RowsAffected()
	if err != nil {
		log.Printf("update RowsAffected failed err : %v\n", err)
	}
	log.Println("受影响的行数=", affectedNum)
}

func deleteRow() {
	sqlStr := "delete from student where id = ?"
	res, err := db.Exec(sqlStr, 5)
	if err != nil {
		log.Printf("update failed err : %v\n", err)
	}
	affectedNum, err := res.RowsAffected()
	if err != nil {
		log.Printf("update RowsAffected failed err : %v\n", err)
	}
	log.Println("受影响的行数=", affectedNum)
}

func insertRow() {
	sqlStr := "insert into student (name, age) values (?,?)"

	//可以同时执行多条
	//数据1
	res, err := db.Exec(sqlStr, "诺诺x", 18)
	id, err := res.LastInsertId()
	if err != nil {
		log.Printf("insert LastInsertId failed err : %v\n", err)
	}
	log.Println("上次插入的id=", id)

	//数据2
	res2, err2 := db.Exec(sqlStr, "荀彧x", 28)
	id2, err2 := res2.LastInsertId()
	if err != nil {
		log.Printf("insert LastInsertId failed err : %v\n", err2)
	}
	log.Println("上次插入的id=", id2)
}

//预编译方式, 先传递sql给mysql生成模板, 再传递参数
func main() {
	if err := initializeDatabase(); err != nil {
		panic(err)
	}
	fmt.Println("connected")

	r := gin.Default()

	r.GET("/user/:id", func(c *gin.Context) {
		idStr := c.Param("id")
		var id, _ = strconv.ParseInt(idStr, 10, 64)
		fmt.Println(id)
		u := querySingleRow((uint64)(id))
		c.JSON(http.StatusOK, gin.H{
			"id":   id,
			"user": u,
		})
	})

	r.GET("/user", func(c *gin.Context) {
		u := queryMultiRow()
		c.JSON(http.StatusOK, gin.H{
			"user": u,
		})
	})

	r.GET("/user/update", func(c *gin.Context) {
		updateRow()
		c.JSON(http.StatusOK, gin.H{
			"msg": "ok",
		})
	})

	r.GET("/user/delete", func(c *gin.Context) {
		deleteRow()
		c.JSON(http.StatusOK, gin.H{
			"msg": "ok",
		})
	})

	r.GET("/user/insert", func(c *gin.Context) {
		insertRow()
		c.JSON(http.StatusOK, gin.H{
			"msg": "ok",
		})
	})

	r.Run(":8080")
}
